﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel;

namespace RESP_IIWaterAssessment
{
    public partial class CultureProfitFormReport : Form
    {
        public CultureProfitFormReport()
        {
            InitializeComponent();
        }

        int regID, avpID, fhID, pfshID = -1, cultID = -1, year;

        private void CultureProfitFormReport_Load(object sender, EventArgs e)
        {
            cbRegion.SelectedIndexChanged -= cbRegion_SelectedIndexChanged;
            cbRegion.DataSource = UtilClass.FillCBRegions();
            cbRegion.DisplayMember = "Name";
            cbRegion.ValueMember = "ID";

            if (InformationClass.isAdmin)
            {
                cbRegion.SelectedIndex = -1;
                cbRegion.Text = string.Empty;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
            }
            else
            {
                cbRegion.Enabled = false;
                regID = InformationClass.regionID;
                cbRegion.SelectedIndexChanged += new EventHandler(cbRegion_SelectedIndexChanged);
                cbRegion.SelectedValue = regID;
                cbRegion_SelectedIndexChanged(cbRegion, null);
                cbAVP.Enabled = true;
                cbAVP.Text = string.Empty;
                //toolStrip1.Visible = false;
            }

            cbCulture.SelectedIndexChanged -= cbCulture_SelectedIndexChanged;
            cbCulture.DataSource = UtilClass.GetCBCultureDataSourse();
            cbCulture.DisplayMember = "Name";
            cbCulture.ValueMember = "ID";
            cbCulture.SelectedIndex = -1;
            cbCulture.SelectedIndexChanged += new EventHandler(cbCulture_SelectedIndexChanged);

            for (int i = 1991; i < 2100; i++)
            {
                cbYear.Items.Add(i);
            }
            cbYear.SelectedIndex = cbYear.FindStringExact(DateTime.Now.Year.ToString());
        }

        private void chbPFSH_CheckedChanged(object sender, EventArgs e)
        {
            groupBox1.Enabled = chbPFSH.Checked;
        }

        private void chbCulture_CheckedChanged(object sender, EventArgs e)
        {
            cbCulture.Enabled = chbCulture.Checked;
        }

        private void cbRegion_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbRegion.SelectedIndex >= 0)
            {
                cbAVP.Enabled = true;
                regID = Convert.ToInt32(cbRegion.SelectedValue);
                cbAVP.SelectedIndexChanged -= cbAVP_SelectedIndexChanged;
                cbAVP.DataSource = UtilClass.FillAVP(regID);
                cbAVP.ValueMember = "ID";
                cbAVP.DisplayMember = "Name";
                cbAVP.SelectedIndex = -1;
                cbAVP.Text = string.Empty;
                cbFH.DataSource = null;
                cbPFSH.DataSource = null;
                cbAVP.SelectedIndexChanged += new EventHandler(cbAVP_SelectedIndexChanged);
            }
        }

        private void cbAVP_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbAVP.SelectedIndex >= 0)
            {
                avpID = Convert.ToInt32(cbAVP.SelectedValue);
                cbFH.SelectedIndexChanged -= cbFH_SelectedIndexChanged;
                cbFH.Enabled = true;
                cbFH.DataSource = DBClass.Farms
                    .Where(wh => wh.AVPID == avpID)
                    .Select(se => new { ID = se.ID, Name = se.Name })
                    .OrderBy(o => o.Name)
                    .ToArray();
                cbFH.DisplayMember = "Name";
                cbFH.ValueMember = "ID";
                cbFH.SelectedIndex = -1;
                cbFH.Text = string.Empty;
                cbPFSH.DataSource = null;
                cbFH.SelectedIndexChanged += new EventHandler(cbFH_SelectedIndexChanged);
            }
        }

        private void cbFH_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbFH.SelectedIndex >= 0)
            {
                fhID = Convert.ToInt32(cbFH.SelectedValue);
                cbPFSH.Enabled = true;
                cbPFSH.SelectedIndexChanged -= cbPFSH_SelectedIndexChanged;
                cbPFSH.DataSource = DBClass.Schools
                    .Where(wh => wh.FarmID == fhID)
                    .Select(se => new { ID = se.ID, Name = se.Name })
                    .OrderBy(o => o.Name)
                    .ToArray();
                cbPFSH.DisplayMember = "Name";
                cbPFSH.ValueMember = "ID";
                cbPFSH.SelectedIndex = -1;
                cbPFSH.Text = string.Empty;
                cbPFSH.SelectedIndexChanged += new EventHandler(cbPFSH_SelectedIndexChanged);
            }
        }

        private void cbPFSH_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbPFSH.SelectedIndex >= 0)
            {
                pfshID = Convert.ToInt32(cbPFSH.SelectedValue);
            }
            else
            {
                pfshID = 0;
            }
        }

        private void cbCulture_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cbCulture.SelectedIndex >= 0)
            {
                cultID = Convert.ToInt32(cbCulture.SelectedValue);

            }
            else
                cultID = 0;
        }

        private void buttonExcel_Click(object sender, EventArgs e)
        {
            if (dgvReport.RowCount > 0)
            {
                MessageBox.Show(msgClass.msgWaiting);
                GetReport();
                excelapp.Visible = true;
            }
        }

        Sheets excelsheets;
        Worksheet excelworksheet;
        Microsoft.Office.Interop.Excel.Application excelapp;
        Workbook excelappworkbook;

        string[] cultureNames = InformationClass.Lang ? DBClass.Cultures.Select(se => se.Name).Distinct().ToArray() : DBClass.Cultures.Select(se => se.Name).Distinct().ToArray();

        private void GetReport()
        {
            excelapp = new Microsoft.Office.Interop.Excel.Application();
            excelapp.SheetsInNewWorkbook = 1;

            if (InformationClass.Lang)
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i24r19.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            else
                excelapp.Workbooks.Open(System.Windows.Forms.Application.StartupPath + @"\Templates\i24r19uz.xls", false, true, Type.Missing, Type.Missing, Type.Missing, true, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            excelappworkbook = excelapp.Workbooks[1];
            excelsheets = excelappworkbook.Worksheets;// .Worksheets[0]; //Sheets 

            int numPage = 1, left = 7, top = 13;

            excelworksheet = (Microsoft.Office.Interop.Excel.Worksheet)excelsheets.get_Item(numPage);

            excelworksheet.Cells.NumberFormat = "@";

            //for (int i = 0; i < dgvReport.Rows.Count; i++)
            //{
            //    for (int j = 0; j < dgvReport.Columns.Count; j++)
            //    {
            //        excelworksheet.Cells[i + top, dgvReport.Columns[j].DisplayIndex + left] = dgvReport[j, i].FormattedValue;
            //        addBorder(excelworksheet.Cells[i + top, dgvReport.Columns[j].DisplayIndex + left]);

            //        if (cultureNames.Contains(dgvReport.Columns[j].Name))
            //        {
            //            excelworksheet.Cells[i + top-1, dgvReport.Columns[j].DisplayIndex + left] = dgvReport.Columns[j].Name;
            //            addBorder(excelworksheet.Cells[i + top-1, dgvReport.Columns[j].DisplayIndex + left]);
            //        }
            //    }
            //    excelworksheet.Cells[i + top, 2] = i + 1;
            //    addBorder(excelworksheet.Cells[i + top, 2]);
            //}

            if (chbPFSH.Checked)
            {
                excelworksheet.Cells[4, 3] = cbRegion.Text;
                excelworksheet.Cells[5, 3] = cbAVP.Text;
                excelworksheet.Cells[6, 3] = cbFH.Text;
                excelworksheet.Cells[7, 3] = cbPFSH.Text;
            }
            if (chbYear.Checked)
            {
                excelworksheet.Cells[8, 3] = cbYear.Text;
            }

            for (int i = 2; i < dgvReport.ColumnCount; i++)
            {
                excelworksheet.get_Range(excelworksheet.Cells[11, 5 + i], excelworksheet.Cells[12, 5 + i]).Merge(Type.Missing);
                addBorder(excelworksheet.Cells[11, 5 + i]);
                addBorder(excelworksheet.Cells[12, 5 + i]);
                excelworksheet.Cells[11, 5 + i] = dgvReport.Columns[i].HeaderText;

                for (int j = 0; j < dgvReport.RowCount; j++)
                {
                    if (dgvReport[1, j].Value.ToString().Equals("SeedQuantity"))
                    {
                        excelworksheet.Cells[13, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[13, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("SeedCost"))
                    {
                        excelworksheet.Cells[14, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[14, 5 + i]);
                        continue;
                    }
                    if(dgvReport[1,j].Value.ToString().Equals("FertilizerQuantity"))
                    {
                        excelworksheet.Cells[15, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[15, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("FertilizerCost"))
                    {
                        excelworksheet.Cells[16, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[16, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("PesticideQuantity"))
                    {
                        excelworksheet.Cells[17, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[17, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("PesticideCost"))
                    {
                        excelworksheet.Cells[18, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[18, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("BioQuantity"))
                    {
                        excelworksheet.Cells[19, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[19, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("BioCost"))
                    {
                        excelworksheet.Cells[20, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[20, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("MWHourQuantity"))
                    {
                        excelworksheet.Cells[21, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[21, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("MWCost"))
                    {
                        excelworksheet.Cells[22, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[22, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("HWHourQuantity"))
                    {
                        excelworksheet.Cells[23, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[23, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("HWCost"))
                    {
                        excelworksheet.Cells[24, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[24, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("Transport"))
                    {
                        excelworksheet.Cells[25, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[25, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("Water"))
                    {
                        excelworksheet.Cells[26, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[26, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("WaterDelivery"))
                    {
                        excelworksheet.Cells[27, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[27, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("Harvest"))
                    {
                        excelworksheet.Cells[29, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[29, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("GrossProduct"))
                    {
                        excelworksheet.Cells[30, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[30, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("LossAmount"))
                    {
                        excelworksheet.Cells[32, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[32, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("TempLoss"))
                    {
                        excelworksheet.Cells[28, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[28, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("FullProfit"))
                    {
                        excelworksheet.Cells[31, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[31, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("ClearProfit"))
                    {
                        excelworksheet.Cells[33, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[33, 5 + i]);
                        continue;
                    }
                    if (dgvReport[1, j].Value.ToString().Equals("ClearProfit"))
                    {
                        excelworksheet.Cells[33, 5 + i] = dgvReport[i, j].Value;
                        addBorder(excelworksheet.Cells[33, 5 + i]);
                        continue;
                    }
                }
            }

            excelworksheet.Columns.AutoFit();
            excelworksheet.Select(numPage);
        }

        private void addBorder(object p)
        {
            Range tmpRange = excelworksheet.get_Range(p, p);
            tmpRange.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
            tmpRange.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
        }

        private void chbYear_CheckedChanged(object sender, EventArgs e)
        {
            cbYear.Enabled = chbYear.Checked;
        }

        private void cbYear_SelectedIndexChanged(object sender, EventArgs e)
        {
            Int32.TryParse(cbYear.Text, out year);
        }

        private void buttonShow_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(DBClass.ConStr);
            SqlCommand com = new SqlCommand("SchoolProfitReport", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.Add("@Lang", SqlDbType.Bit).Value = InformationClass.Lang;
            com.Parameters.Add("@SchoolID", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@CultureID", SqlDbType.Int).IsNullable = true;
            com.Parameters.Add("@Year", SqlDbType.Int).IsNullable = true;

            if (chbPFSH.Checked && pfshID > 0)
                com.Parameters["@SchoolID"].Value = pfshID;
            else
                com.Parameters["@SchoolID"].Value = DBNull.Value;

            if (chbCulture.Checked && cultID > 0)
                com.Parameters["@CultureID"].Value = cultID;
            else
                com.Parameters["@CultureID"].Value = DBNull.Value;

            if (chbYear.Checked && year >= 1991)
                com.Parameters["@Year"].Value = year;
            else
                com.Parameters["@Year"].Value = DBNull.Value;

            System.Data.DataTable tab = new System.Data.DataTable("Rep");
            SqlDataAdapter adapter = new SqlDataAdapter(com);
            try
            {
                adapter.Fill(tab);
                dgvReport.DataSource = tab;

                if (InformationClass.Lang)
                {
                    dgvReport.Columns["ProfitYear"].HeaderText = "Год";
                    dgvReport.Columns["Indicator"].HeaderText = "Показатель";
                }
                else
                {
                    dgvReport.Columns["ProfitYear"].HeaderText = "Йил";
                    dgvReport.Columns["Indicator"].HeaderText = "Кўрсаткичлар";
                }
                dgvReport.AutoResizeColumns();
            }
            catch (Exception ex)
            {
                MessageBox.Show(msgClass.msgNoData, msgClass.msgAchtung);
                dgvReport.DataSource = null;
            }
        }

        private void dgvReport_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
        {
            if (dgvReport.Columns.Contains("Indicator"))
            {
                if (e.ColumnIndex.Equals(dgvReport.Columns["Indicator"].Index) && e.Value != null)
                {
                    switch (e.Value.ToString())
                    {
                        case "SeedQuantity":
                            e.Value = InformationClass.Lang ? "Семена (кг/га)" : "Уруғлар (кг/га)";
                            break;
                        case "SeedCost":
                            e.Value = InformationClass.Lang ? "Семена (сум/га)" : "Уруғлар (сўм/га)";
                            break;
                        case "FertilizerQuantity":
                            e.Value = InformationClass.Lang ? "Удобрения (кг/га)" : "Ўғитлар (кг/га)";
                            break;
                        case "FertilizerCost":
                            e.Value = InformationClass.Lang ? "Удобрения (сум/га)" : "Ўғитлар (сўм/га)";
                            break;
                        case "PesticideQuantity":
                            e.Value = InformationClass.Lang ? "Ядохимикаты (кг/га)" : "Заҳарли химикатлар (кг/га)";
                            break;
                        case "PesticideCost":
                            e.Value = InformationClass.Lang ? "Ядохимикаты (сум/га)" : "Заҳарли химикатлар (сўм/га)";
                            break;
                        case "BioQuantity":
                            e.Value = InformationClass.Lang ? "Биологические (штук. грамм/га)" : "Биологик (дона грамм/га)";
                            break;
                        case "BioCost":
                            e.Value = InformationClass.Lang ? "Биологические (сум/га)" : "Биологик (сўм/га)";
                            break;
                        case "MWHourQuantity":
                            e.Value = InformationClass.Lang ? "Механизированный труд (маш-час/га)" : "Механизациялашган меҳнат (маш-час/га)";
                            break;
                        case "MWCost":
                            e.Value = InformationClass.Lang ? "Механизированный труд (сум/га)" : "Механизациялашган меҳнат (сўм/га)";
                            break;

                        case "HWHourQuantity":
                            e.Value = InformationClass.Lang ? "Ручной труд (чел.дней/га)" : "Қўл меҳнати (киши кунлар/га)";
                            break;
                        case "HWCost":
                            e.Value = InformationClass.Lang ? "Ручной труд (сум/га)" : "Қўл меҳнати (сўм/га)";
                            break;

                        case "Transport":
                            e.Value = InformationClass.Lang ? "Транспорт (сум/га)" : "Транспорт (сўм/га)";
                            break;
                        case "Water":
                            e.Value = InformationClass.Lang ? "Вода (тыс.м3/га)" : "Сув (минг м3/га)";
                            break;
                        case "WaterDelivery":
                            e.Value = InformationClass.Lang ? "Плата в АВП за доставку воды (сум/га)" : "Сув таъминоти учун СИУга тўловлар (сўм/га)";
                            break;

                        case "Harvest":
                            e.Value = InformationClass.Lang ? "Урожай (тонн/га)" : "Ҳосил (сўм/га)";
                            break;
                        case "GrossProduct":
                            e.Value = InformationClass.Lang ? "Валовой продукт (сўм/га)" : "Ялпи маҳсулот (сўм/га)";
                            break;
                        case "LossAmount":
                            e.Value = InformationClass.Lang ? "Постоянные затраты (сум/га)" : "Доимий ҳаражатлар (сўм/га)";
                            break;
                        case "TempLoss":
                            e.Value = InformationClass.Lang ? "Переменные затраты (сум/га)" : "Ўзгарувчан ҳаражатлар (сўм/га)";
                            break;
                        case "FullProfit":
                            e.Value = InformationClass.Lang ? "Общая прибыль (сум/га)" : "Умумий даромад (сўм/га)";
                            break;
                        case "ClearProfit":
                            e.Value = InformationClass.Lang ? "Чистая прибыль (сум/га)" : "Соф фойда (сўм/га)";
                            break;

                        default:
                            break;
                    }
                }
            }
        }

        private void buttonPrint_Click(object sender, EventArgs e)
        {
            if (dgvReport.RowCount > 0)
                PrintDGV.Print_DataGridView(dgvReport);
        }
    }
}
